<?php
require_once($_SERVER['DOCUMENT_ROOT'].'/ukkepuk/source/php/classes/validation/ActionResult.php');
require_once($_SERVER['DOCUMENT_ROOT'].'/ukkepuk/source/php/classes/service/KidService.php');

	class UkkepukDao {
		private $mysqli;

		public function __construct(){
			//echo "construct";
			$this->mysqli = new mysqli('localhost', 'root', 'password', 'ukkepuk');
			if($this->mysqli->connect_error){
				die('Connect Error (' . $this->mysqli->connec_errno . ') ' . $mysqli->connect_error);
			}
	
			if (mysqli_connect_error()) {
				die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
			}
			
		}
		public function __destruct(){
			//echo "destruct";
			$this->mysqli->close();
		}
	
		//Save kid to the database.
		public function saveNewKid($kid){
			//Save address
			$queryAddress = $this->mysqli->prepare("INSERT INTO address(STREET, NUMBER, POSTAL, CITY) VALUES(?,?,?,?)");
			$queryAddress->bind_param("ssss", $kid->street, $kid->number, $kid->postal, $kid->city);
			$queryAddress->execute();
			$kid->addressId = $queryAddress->insert_id;
			$queryAddress->close();
			
			//Save kid with link to address
			$queryKid = $this->mysqli->prepare("INSERT INTO kids(NAME, FIRST_NAME, ADDRESS_ID) VALUES(?,?,?)");
			$queryKid->bind_param("ssi", $kid->name, $kid->firstname, $kid->addressId );
			$queryKid->execute();
			$kid->id = $queryKid->insert_id;
			$queryKid->close();
			
			//Save communication
			$queryKidCommunication = $this->mysqli->prepare("INSERT INTO communication(TYPE_ID, VALUE) VALUES(?,?)");
			$queryKidCommunicationLink = $this->mysqli->prepare("INSERT INTO kids_communication(KIDS_ID, COMMUNICATION_ID) VALUES(?,?)");
			foreach($kid->communication as $com){
				//save global communication
				echo "type " . $com->type . " and value " . $com->value;
				$queryKidCommunication->bind_param("ss", $com->type, $com->value );
				$queryKidCommunication->execute();
				$queryKidCommunicationID = $queryKidCommunication->insert_id;
				
				//link communication to kid
				$queryKidCommunicationLink->bind_param("ii",$kid->id, $queryKidCommunicationID);
				$queryKidCommunicationLink->execute();
			}
			$queryKidCommunication->close();
			$queryKidCommunicationLink->close();
			
			//Save family
			$queryKidFamily = $this->mysqli->prepare("INSERT INTO family(NAME, FIRST_NAME) VALUES (?,?)");
			$queryKidFamilyLink = $this->mysqli->prepare("INSERT INTO kid_family_relation(KIDS_ID, FAMILY_ID, RELATION_TYPE_ID) VALUES (?,?,?)");
			
			$queryKidFamCom = $this->mysqli->prepare("INSERT INTO communication(TYPE_ID, VALUE) VALUES(?,?)");
			$queryKidFamComLink = $this->mysqli->prepare("INSERT INTO family_communication(FAMILY_ID, COMMUNICATION_ID) VALUES(?,?)");
			foreach($kid->family as $fam){
				$queryKidFamily->bind_param("ss", $fam->name, $fam->firstname );
				$queryKidFamily->execute();
				$queryKidFamilyID = $queryKidFamily->insert_id;
				$queryKidFamilyLink->bind_param("iii", $kid->id, $queryKidFamilyID, $fam->type);
				$queryKidFamilyLink->execute();
				
				//family communication
				foreach($fam->communication as $famcom){
					$queryKidFamCom->bind_param("ss", $famcom->type, $famcom->value );
					$queryKidFamCom->execute();
					$queryKidFamComID = $queryKidFamCom->insert_id;
					$queryKidFamComLink->bind_param("ii",$queryKidFamilyID, $queryKidFamComID);
					$queryKidFamComLink->execute();
				}	
			}
			$queryKidFamily->close();
			$queryKidFamilyLink->close();
			$queryKidFamCom->close();
			$queryKidFamComLink->close();
		}
		
		public function saveRecipe($recipe){
			$queryRecipe = $this->mysqli->prepare("INSERT INTO recipies(TITLE, DESCRIPTION) VALUES (?,?)");
			$queryRecipe->bind_param("ss", $recipe->title, $recipe->description);
			$queryRecipe->execute();
			$queryRecipe->close();
		}
		
		
		//return an array of all kids
		public function readAllKids() {
			$result = Array();
			
			$queryKid = $this->mysqli->prepare("SELECT k.ID, k.NAME, k.FIRST_NAME, k.PROFILE_IMAGE FROM kids k");
			$queryKid->execute();
			$queryKid->bind_result($id, $name, $firstname, $profileImage);
			while($queryKid->fetch()){
				$kid = new Kid();
				$kid->id = $id;
				$kid->name = $name;
				$kid->firstname = $firstname;
				$kid->profileImage = $profileImage;
				array_push($result, $kid);
			}
			$queryKid->close();
			
			return $result;
		}
		
		public function saveDiaperForKid($diaper, $kid){
			//create a result action object. this contains info about the result of the execution of this method
			$actionResult = new ActionResult();
			//set the executed action in the result
			$actionResult->savetype = "saveDiperForKid";
			//save diaper info for kid
			$saveDiaper = $this->mysqli->prepare("INSERT INTO diapers(KIDS_ID, TIMESTAMP, TYPE) VALUES (?,?,?)");
			$dateTime = $diaper->getDateTime();
			$saveDiaper->bind_param("isi", $kid->id, $dateTime, $diaper->type);
			if($saveDiaper->execute()){
				//if execute success, set succes to true
				$actionResult->success = true;
				//and add diaper in the result
				$actionResult->addActionParameter($diaper);
			}else {
				//if error during execution set reason of error in the result object
				$actionResult->error = $this->mysqli->error;
			}
			//close the statement
			$saveDiaper->close();
			
			//return the actionresult
			return $actionResult;
		}
        
        public function readNote($kidNote){
            $actionResult = new ActionResult();
			$actionResult->savetype = "readNote";
		
            $queryNoteCount = $this->mysqli->prepare("SELECT REPORT FROM kid_report WHERE kids_ID = ? and date_format(TIMESTAMP, '%Y%m%d') = ?");
            $dateKey = $kidNote->getDateKey();
            $queryNoteCount->bind_param('is', $kidNote->id, $dateKey);

            if($queryNoteCount->execute()){
                $actionResult->success = true;
                $queryNoteCount->bind_result($report);
                $queryNoteCount->fetch();
                $kidNote->note = $report;
                $actionResult->addActionParameter($kidNote);
            }else {
                $actionResult->error = $this->mysqli->error;
                
            }
            return $actionResult;
        }
        
        public function saveNote($kidNote){
            $actionResult = new ActionResult();
			$actionResult->savetype = "saveNote";
		
            //first check if there is a record for kid and day
            $queryNoteCount = $this->mysqli->prepare("SELECT ID FROM kid_report WHERE kids_ID = ? and date_format(TIMESTAMP, '%Y%m%d') = ?");
            $dateKey = $kidNote->getDateKey();
            $queryNoteCount->bind_param('is', $kidNote->id, $dateKey);
            if($queryNoteCount->execute()){
                $queryNoteCount->store_result(); 
                $queryNoteCount->bind_result($id);
                $queryNoteCount->fetch();
                if($id){
                    $queryNoteUpdate = $this->mysqli->prepare("UPDATE kid_report SET REPORT = ? WHERE ID = ?");
                    
                    $queryNoteUpdate->bind_param('si', $kidNote->note, $id);
                    if($queryNoteUpdate->execute()){
                        $actionResult->success = true;
                    }else{
                        $actionResult->error = $this->mysqli->error;
                    }
                    $queryNoteUpdate->close();
                }else {
                    $queryNote = $this->mysqli->prepare("INSERT INTO kid_report(kids_ID, REPORT, TIMESTAMP) VALUES (?,?,?)");
                    $dateTime = $kidNote->getDate();
                    $queryNote->bind_param('iss', $kidNote->id, $kidNote->note, $dateTime);

                    if($queryNote->execute()){
                        $actionResult->success = true;
                    }else{
                        $actionResult->error = $this->mysqli->error;
                    }
                    $queryNote->close();
                }
                
                
            }else{
                $actionResult->error = $this->mysqli->error;
            }
            $queryNoteCount->free_result();
            $queryNoteCount->close();
            
            //code here
            //if there is, update.
            //code here
            //if not, insert
            return $actionResult;
        }
		
		public function saveDrinkForKid($drink, $kid){
			$actionResult = new ActionResult();
			$actionResult->savetype = "saveLiquidForKid";
		
			$queryDrink = $this->mysqli->prepare("INSERT INTO drinks(KIDS_ID, DRINK_TYPES_ID, AMOUNT, TIMESTAMP) VALUES (?,?,?,?)");
			$dateTime = $drink->getDateTime();
			$queryDrink->bind_param("iiis", $kid->id, $drink->typeId, $drink->amount, $dateTime);
			if($queryDrink->execute()){
                //double check food title saved
                $queryDrinkType = $this->mysqli->prepare("SELECT TYPE FROM drink_types WHERE ID = ?");
                $queryDrinkType->bind_param("i", $drink->typeId);
                
                if($queryDrinkType->execute()){
                    $actionResult->success = true;
                    $queryDrinkType->bind_result($type);
                    $queryDrinkType->fetch();
                    $drink->typeString = $type;
                    
                    $actionResult->addActionParameter($drink);
                    
                }else { 
                    $actionResult->error = $this->mysqli->error;
                }
				
                
			} else {
				$actionResult->error = $this->mysqli->error;
			}
			$queryDrink->close();
		
			return $actionResult;
		}
		
		public function saveFoodForKid($food, $kid){
			$actionResult = new ActionResult();
			$actionResult->savetype = "saveFoodForKid";
		
			$queryFood = $this->mysqli->prepare("INSERT INTO food(KIDS_ID, RECIPE_ID, TIMESTAMP, SCORE) VALUES (?,?,?,?)");
			$dateTime = $food->getDateTime();
			$queryFood->bind_param("iisi", $kid->id, $food->menuId, $dateTime, $food->score);
			if($queryFood->execute()){
				
                
                //double check food title saved
                $queryFoodTitle = $this->mysqli->prepare("SELECT TITLE FROM recipe WHERE ID = ?");
                $queryFoodTitle->bind_param("i", $food->menuId);
                if($queryFoodTitle->execute()){
                    $actionResult->success = true;
                    $queryFoodTitle->bind_result($title);
                    $queryFoodTitle->fetch();
                    $food->menuTitle = $title;
                    
                }else { 
                    $actionResult->error = $this->mysqli->error;
                }
                
                
				$actionResult->addActionParameter($food);
			} else {
				$actionResult->error = $this->mysqli->error;
			}
			$queryFood->close();
		
			return $actionResult;
		}
		
		public function saveSleepForKid($sleep, $kid){
			$actionResult = new ActionResult();
			$actionResult->savetype = "saveSleepForKid";
			//find kid where to is null
			$querySleepToNull = $this->mysqli->prepare("SELECT ID FROM sleep WHERE SLEEP_TO is null AND KIDS_ID = ?");
			$querySleepToNull->bind_param("i", $kid->id);
			$querySleepToNull->execute();
			$querySleepToNull->store_result(); 
			$querySleepToNull->bind_result($sleepId);
			if($querySleepToNull->fetch()){
				$querySleepUpdateTo = $this->mysqli->prepare("UPDATE sleep SET SLEEP_TO = ? WHERE ID = ?");	
				$dateTime = $sleep->getDateTime();
				$sleepIdFound = $sleepId;
				//echo $this->mysqli->error
				$querySleepUpdateTo->bind_param("si", $dateTime, $sleepIdFound);
				if($querySleepUpdateTo->execute()){
					$actionResult->success = true;
					$sleep->begin = false;
					$actionResult->addActionParameter($sleep);
				}else {
					$actionResult->error = $this->mysqli->error;
				}
				$querySleepUpdateTo->close();
			}else {
				$dateTime = $sleep->getDateTime();
				$querySleepInsertFrom = $this->mysqli->prepare("INSERT INTO sleep (KIDS_ID, SLEEP_FROM) VALUES (?, ?)");	
				$querySleepInsertFrom->bind_param("is", $kid->id, $dateTime);
				if($querySleepInsertFrom->execute()){
					$actionResult->success = true;
					$actionResult->addActionParameter($sleep);
				}else {
					$actionResult->error = $this->mysqli->error;
				}
				$querySleepInsertFrom->close();
			}
			$querySleepToNull->free_result();
			$querySleepToNull->close();
			
			return $actionResult;
			//if found, fill sleep in to field
			//if not found create new row
		}
		
		//read kid with creche info (dipers, food, milk,... of today only)
		//<kid name="" firstname="" profileImage="">
		//  <dipers>
		//		<diper time="" type="">
		//	</dipers>
		//</kid>
		public function readKidInfoXml($kidId){
            $date = new DateTime();
			$today = $date->format('Ymd');		
            
            //READ KID INFO
			$queryKid = $this->mysqli->prepare("SELECT k.NAME, k.FIRST_NAME, k.PROFILE_IMAGE FROM kids k WHERE k.ID = ?");
			$queryKid->bind_param("i", $kidId);
			$queryKid->execute();
			$queryKid->bind_result($name, $firstname, $profileImage);
			$queryKid->fetch();
			$result = "<kid id=\"$kidId\" name=\"$name\" firstname=\"$firstname\" profileImage=\"$profileImage\" date=\"$today\">";
			$queryKid->close();
			
			
			
			
			//READ DIAPERS FOR TODAY
			$queryKidDiapers =  $this->mysqli->prepare("SELECT date_format(d.TIMESTAMP, '%H:%i'), d.TYPE FROM diapers d WHERE date_format(d.TIMESTAMP, '%Y%m%d') = ? AND d.KIDS_ID = ? ORDER BY d.TIMESTAMP");
			$queryKidDiapers->bind_param("si", $today, $kidId);
			$queryKidDiapers->execute();
			$queryKidDiapers->bind_result($time, $type);
			$result .= "<diapers>";
			while($queryKidDiapers->fetch()){
				$result .= "<diaper time=\"".$time."\" type=\"".$type."\"/>";
			}
			$result .= "</diapers>";
			
			//READ SLEEP FOR TODAY
			$queryKidSleep = $this->mysqli->prepare("SELECT date_format(s.SLEEP_FROM, '%H:%i'), date_format(s.SLEEP_TO, '%H:%i') FROM sleep s WHERE date_format(s.SLEEP_FROM, '%Y%m%d') = ? AND s.KIDS_ID = ? ORDER BY s.SLEEP_FROM");
			$queryKidSleep->bind_param("si", $today, $kidId);
			$queryKidSleep->execute();
			$queryKidSleep->bind_result($sleepFrom, $sleepTo);
			$result .= "<sleeps>";
			while($queryKidSleep->fetch()){
				$result .= "<sleep sleepFrom=\"$sleepFrom\" sleepTo=\"$sleepTo\" />";
			}
			$result .= "</sleeps>";
			
			//READ DRINK FOR TODAY
			$queryKidDrink = $this->mysqli->prepare("SELECT dt.TYPE, AMOUNT, date_format(d.TIMESTAMP, '%H:%i') FROM drinks d, drink_types dt WHERE d.DRINK_TYPES_ID = dt.ID AND date_format(d.TIMESTAMP, '%Y%m%d') = ? AND d.KIDS_ID = ? ORDER BY d.TIMESTAMP");
			$queryKidDrink->bind_param("si", $today, $kidId);
			$queryKidDrink->execute();
			$queryKidDrink->bind_result($drinkType, $amount, $time);
			$result .= "<drinks>";
			while($queryKidDrink->fetch()){
				$result .= "<drink time=\"".$time."\" amount=\"".$amount."\" type=\"".$drinkType."\"/>";
			}
			$result .= "</drinks>";
			
			
			//READ FOOD FOR TODAY
                        $queryKidFood = $this->mysqli->prepare("SELECT r.TITLE, date_format(f.TIMESTAMP, '%H:%i'), f.score FROM food f, recipe r WHERE f.RECIPE_ID = r.ID AND date_format(f.TIMESTAMP, '%Y%m%d') = ? AND f.KIDS_ID = ? ORDER BY f.TIMESTAMP");
			$queryKidFood->bind_param("si", $today, $kidId);
			$queryKidFood->execute();
			$queryKidFood->bind_result($title, $time, $score);
			$result .= "<foods>";
			while($queryKidFood->fetch()){
				$result .= "<food time=\"".$time."\" title=\"".$title."\" score=\"".$score."\"/>";
			}
			$result .= "</foods>";
			
                        
                        
			$result .= "</kid>";
			return $result;
			
		}
		
		//read a kid from the db and store all his info in an xml
		//contact info, family, ...
		public function readKidXml($kidId){
			//READ KID INFO
			$queryKid = $this->mysqli->prepare("SELECT k.NAME, k.FIRST_NAME, k.PROFILE_IMAGE, a.STREET, a.NUMBER, a.POSTAL, a.CITY FROM kids k, address a WHERE k.ADDRESS_ID = a.ID AND k.ID = ?");
			$queryKid->bind_param("i", $kidId);
			$queryKid->execute();
			$queryKid->bind_result($name, $firstname, $profileImage, $street, $number, $postal, $city);
			$queryKid->fetch();
			$result = "<kid name=\"$name\" firstname=\"$firstname\" profileImage=\"$profileImage\" street=\"$street\" number=\"$number\" postal=\"$postal\" city=\"$city\">";
			$queryKid->close();
			
			//READ COMMUNICATION FROM KID
			$queryCommunication = $this->mysqli->prepare("SELECT ct.TYPE, c.VALUE FROM kids_communication kc, communication c, communication_types ct WHERE kc.kids_ID = ? AND kc.communication_ID = c.ID AND c.TYPE = ct.ID");
			$queryCommunication->bind_param("i", $kidId);
			$queryCommunication->execute();
			$queryCommunication->bind_result($type, $value);
			$result .= "<communications>";
			while($queryCommunication->fetch()){
				$result .= "<communication type=\"$type\" value=\"$value\"/>";
			}
			$result .= "</communications>";
			$queryCommunication->close();
			
			//READ FAMILY FROM KID
			$queryFamily = $this->mysqli->prepare("SELECT f.ID, rt.TYPE, f.NAME, f.FIRST_NAME FROM kid_family_relation kfr, family f, relation_type rt WHERE kfr.KIDS_ID = ? AND f.ID = kfr.FAMILY_ID AND rt.ID = kfr.RELATION_TYPE_ID");
			$queryFamilyCommunication = $this->mysqli->prepare("SELECT ct.TYPE, c.VALUE FROM family_communication fc, communication c, communication_types ct WHERE fc.family_ID = ? AND fc.communication_ID = c.ID AND c.TYPE = ct.ID");
			$queryFamily->bind_param("i", $kidId);
			$queryFamily->execute();
			$queryFamily->store_result(); //Results should be stored if not all results are read before performing other query.
			$queryFamily->bind_result($famId, $type, $name, $firstname);
			$result .= "<people-relation>";
			while($queryFamily->fetch()){
				$result .= "<relation type=\"$type\" name=\"$name\" firstname=\"$firstname\">";
				//READ FAMILY COMMUNICATION
				$queryFamilyCommunication->bind_param("i", $famId);
				$queryFamilyCommunication->execute();
				$queryFamilyCommunication->bind_result($type, $value);
				$result .= "<communications>";
				while($queryFamilyCommunication->fetch()){
					$result .= "<communication type=\"$type\" value=\"$value\"/>";
				}	
				$result .= "</communications>";
				$result .= "</relation>";
			}
			$result .= "</people-relation>";
			$queryFamilyCommunication->close();
			$queryFamily->free_result();
			$queryFamily->close();
			$result .= "</kid>";
			
			return $result;
		}
	}
?>